<?php
class ModelCatalogProduct extends Model {
	public function updateViewed($product_id) {
		$this->db->query("UPDATE " . DB_PREFIX . "product SET viewed = (viewed + 1) WHERE product_id = '" . (int)$product_id . "'");
	}
	
	public function getProduct($product_id) {
		$languageId = (int)$this->config->get('config_language_id');
		
		$sql = "SELECT *, pd.name AS name, p.image, " . 
			"(SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.quantity = '1' AND pd2.date_start < NOW() " . 
				"AND pd2.date_end > NOW() ORDER BY pd2.price ASC LIMIT 1) AS discount, " . 
			"(SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.date_start < NOW() AND ps.date_end > NOW() " . 
				"AND ps.lang_id = '" . $languageId . "'ORDER BY ps.price ASC LIMIT 1) AS special, " . 
			"(SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . $languageId . 
				"') AS stock_status, " . 
			"(SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . 
				$languageId . "') AS weight_class, " . 
			"(SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . 
				$languageId . "') AS length_class, " . 
			"(SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, " . 
			"(SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, " . 
			"p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . 
			DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id AND pd.language_id = '" . $languageId . "') WHERE p.product_id = '" . 
			(int)$product_id . "' AND p.status = '1' AND p.date_available <= NOW()";
		
		$query = $this->db->query($sql);	
		if ($query->num_rows) {
			$query->row['price'] = ($query->row['discount'] ? $query->row['discount'] : $query->row['price']);
			$query->row['rating'] = (int)$query->row['rating'];
			
			return $query->row;
		} else {
			return false;
		}
	}

	public function getProducts($data = array()) {
		
		$cache = md5(http_build_query($data));
		
		$product_data = $this->cache->get('product.' . (int)$this->config->get('config_language_id') . '.' . $cache);
		
		if (!$product_data) {
			if(isset($data['keyword'])){
				$keyword = $this->db->escape(utf8_strtolower($data['keyword']));
			}else{
				$keyword = false;
			}
			if(isset($data['category_id'])){
				$categoryIds = $data['category_id'];
				if ($categoryIds) {
					$this->load->model('catalog/category');
					$categories = $this->model_catalog_category->getCategoriesByParentId($categoryIds);
					foreach ($categories as $category_id) {
						$categoryIds .= "," . $category_id;
					}
				}
			}else{
				$categoryIds = false;
			}
			
			$languageId = (int)$this->config->get('config_language_id');
			
			$sql = "SELECT p.product_id, " . 
					"(SELECT AVG(rating) AS total FROM " . 
						DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . 
				DB_PREFIX . "product p LEFT JOIN " . 
				DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id AND pd.language_id = '" . $languageId . "') LEFT JOIN " . 
				DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) LEFT JOIN " . 
				DB_PREFIX . "category_description cd ON (p2c.category_id = cd.category_id AND cd.language_id = '" . $languageId . "') LEFT JOIN " . 
				DB_PREFIX . "product_tag pt ON (p.product_id = pt.product_id AND pt.language_id = '" . $languageId . "')" . 
				" WHERE p.status = '1' AND p.date_available <= NOW()";
			
			if ($categoryIds) {
				$sql .= " AND p2c.category_id IN (" . $categoryIds . ")";			
			}
					
			if ($keyword) {
				if(preg_match("/^\d*$/",$keyword)){
					$sql .= " AND p.product_id LIKE '%" . $keyword . "%'";
				} else {
					$sql .= " AND (LCASE(p.model) LIKE '%" . $keyword . 
						"%' OR LCASE(pd.name) LIKE '%" . $keyword . 
						"%' OR LCASE(pd.description) LIKE '%" . $keyword . 
						"%' OR LCASE(cd.name) LIKE '%" . $keyword . 
						"%' OR LCASE(pt.tag) LIKE '%" . $keyword . "')" ;	
				}
			}
			
			$sql .= " GROUP BY p.product_id";
			
			if (isset($data['order']) && ($data['order'] == 'ASC')) {
				$sort_a_d = "ASC";
			} else {
				$sort_a_d = "DESC";
			}
			
			$sort_data = array(
				'p.price',
				'rating',
				'p.date_added'
			);	
			$sql .= " ORDER BY p.sort_order ASC";
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				if ($data['sort'] == 'rating') {
					$sql .= ", p.viewed " . $sort_a_d . ", rating " . $sort_a_d;
				} else {
					$sql .= ", " . $data['sort'] . " " . $sort_a_d;
				}
			}
		
			if (!isset($data['start']) || $data['start'] < 0) {
				$data['start'] = 0;
			}
			if (!isset($data['limit']) || $data['limit'] < 0) {
				$data['limit'] = 24;
			}
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
						
			$product_data = array();
					
			$query = $this->db->query($sql);
		
			foreach ($query->rows as $result) {
				$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
			}
			
			$this->cache->set('product.' . (int)$this->config->get('config_language_id') . '.' . $cache, $product_data);
		}
		
		return $product_data;
	}
	
	public function getProductSpecials($data = array()) {
		$cache = md5(http_build_query($data));
		
		$product_data = $this->cache->get('productSpecials.' . (int)$this->config->get('config_language_id') . '.' . $cache);
		
		if (!$product_data) {
			$sql = "SELECT DISTINCT ps.product_id, (SELECT AVG(rating) FROM " . 
				DB_PREFIX . "review r1 WHERE r1.product_id = ps.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . 
				DB_PREFIX . "product_special ps LEFT JOIN " . 
				DB_PREFIX . "product p ON (ps.product_id = p.product_id) WHERE";
				
			if (isset($data['promotion_id'])) {
				$sql .= " ps.promotion_activity_id = '" . $data['promotion_id'] . "' AND";
			}	
			$sql .= " p.status = '1' AND p.date_available <= NOW()" . 
				" AND ps.date_start < NOW() AND ps.date_end > NOW() GROUP BY ps.product_id";
	
			if (isset($data['order']) && ($data['order'] == 'ASC')) {
				$sort_a_d = "ASC";
			} else {
				$sort_a_d = "DESC";
			}
			
			$sort_data = array(
				'p.price',
				'rating',
				'p.date_added'
			);	
			$sql .= " ORDER BY p.sort_order ASC";
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				if ($data['sort'] == 'rating') {
					$sql .= ", rating " . $sort_a_d . ", p.viewed " . $sort_a_d;
				} else {
					$sql .= ", " . $data['sort'] . " " . $sort_a_d;
				}
			}
		
			if (!isset($data['start']) || $data['start'] < 0) {
				$data['start'] = 0;
			}
			if (!isset($data['limit']) || $data['limit'] < 0) {
				$data['limit'] = 24;
			}
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
	
			$product_data = array();
			
			$query = $this->db->query($sql);
			
			foreach ($query->rows as $result) { 		
				$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
			}
			
			$this->cache->set('productSpecials.' . (int)$this->config->get('config_language_id') . '.' . $cache, $product_data);
		}
		return $product_data;
	}
		
	public function getLatestProducts($data = array()) {		
		$cache = md5(http_build_query($data));
		
		$product_data = $this->cache->get('latestProducts.' . (int)$this->config->get('config_language_id') . '.' . $cache);
		
		if (!$product_data) {	
			$sql = "SELECT p.product_id, (SELECT AVG(rating) FROM " . 
				DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . 
				DB_PREFIX . "product p LEFT JOIN " . 
				DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) WHERE p.status = '1' AND p.date_available <= NOW()";
					
			if(isset($data['category_id'])){
				$categoryIds = $data['category_id'];
				if ($categoryIds) {
					$this->load->model('catalog/category');
					$categories = $this->model_catalog_category->getCategoriesByParentId($categoryIds);
					foreach ($categories as $category_id) {
						$categoryIds .= "," . $category_id;
					}
					$sql .= " AND p2c.category_id IN (" . $categoryIds . ")";
				}
			}
			if (isset($data['order']) && ($data['order'] == 'ASC')) {
				$sort_a_d = "ASC";
			} else {
				$sort_a_d = "DESC";
			}
			
			$sort_data = array(
				'p.price',
				'rating',
				'p.date_added'
			);	
			$sql .= " GROUP BY p.product_id ORDER BY p.sort_order ASC";
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				if ($data['sort'] == 'rating') {
					$sql .= ", rating " . $sort_a_d . ", p.viewed " . $sort_a_d;
				} else {
					$sql .= ", " . $data['sort'] . " " . $sort_a_d;
				}
			}
		
			if (!isset($data['start']) || $data['start'] < 0) {
				$data['start'] = 0;
			}
			if (!isset($data['limit']) || $data['limit'] < 0) {
				$data['limit'] = 24;
			}
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
	
			$product_data = array();
			
			$query = $this->db->query($sql);
			
			foreach ($query->rows as $result) { 		
				$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
			}
		
			$this->cache->set('latestProducts.' . (int)$this->config->get('config_language_id') . '.' . $cache, $product_data);
		}
		return $product_data;
	}
	
	public function getPopularProducts($limit) {
		$cache = md5(http_build_query($data));
		
		$product_data = $this->cache->get('popularProducts.' . (int)$this->config->get('config_language_id') . '.' . $cache);
		
		if (!$product_data) {
			$product_data = array();
			
			$query = $this->db->query("SELECT p.product_id FROM " . 
				DB_PREFIX . "product p WHERE p.status = '1' AND p.date_available <= NOW() ORDER BY p.viewed, p.date_added DESC LIMIT " . (int)$limit);
			
			foreach ($query->rows as $result) { 		
				$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
			}
		
			$this->cache->set('popularProducts.' . (int)$this->config->get('config_language_id') . '.' . $cache, $product_data);
		}			 	 		
		return $product_data;
	}

	public function getBestSellerProducts($data = array()) {
		$cache = md5(http_build_query($data));
		
		$product_data = $this->cache->get('bestSellerProducts.' . (int)$this->config->get('config_language_id') . '.' . $cache);
		
		if (!$product_data) {	
			$sql = "SELECT op.product_id, COUNT(*) AS total FROM " . 
				DB_PREFIX . "order_product op LEFT JOIN `" . 
				DB_PREFIX . "product` p ON (op.product_id = p.product_id) LEFT JOIN " . 
				DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) WHERE p.status = '1' AND p.date_available <= NOW()";
					
			if(isset($data['category_id'])){
				$categoryIds = $data['category_id'];
				if ($categoryIds) {
					$this->load->model('catalog/category');
					$categories = $this->model_catalog_category->getCategoriesByParentId($categoryIds);
					foreach ($categories as $category_id) {
						$categoryIds .= "," . $category_id;
					}
					$sql .= " AND p2c.category_id IN (" . $categoryIds . ")";
				}
			}
			
			if (isset($data['order']) && ($data['order'] == 'ASC')) {
				$sort_a_d = "ASC";
			} else {
				$sort_a_d = "DESC";
			}
			$sort_data = array(
				'p.price',
				'rating',
				'p.date_added'
			);	
			$sql .= " GROUP BY op.product_id ORDER BY p.sort_order ASC";
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				if ($data['sort'] == 'rating') {
					$sql .= ", total " . $sort_a_d . ", p.viewed " . $sort_a_d;
				} else {
					$sql .= ", " . $data['sort'] . " " . $sort_a_d;
				}
			}
		
			if (!isset($data['start']) || $data['start'] < 0) {
				$data['start'] = 0;
			}
			if (!isset($data['limit']) || $data['limit'] < 0) {
				$data['limit'] = 24;
			}
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			
			$product_data = array();
			
			$query = $this->db->query($sql);
			
			foreach ($query->rows as $result) { 		
				$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
			}
		
			$this->cache->set('bestSellerProducts.' . (int)$this->config->get('config_language_id') . '.' . $cache, $product_data);
		}
		return $product_data;
	}
	
	public function getProductAttributes($product_id) {
		$product_attribute_group_data = array();
		
		$product_attribute_group_query = $this->db->query("SELECT ag.attribute_group_id, agd.name FROM " . 
			DB_PREFIX . "product_attribute pa LEFT JOIN " . 
			DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . 
			DB_PREFIX . "attribute_group ag ON (a.attribute_group_id = ag.attribute_group_id) LEFT JOIN " . 
			DB_PREFIX . "attribute_group_description agd ON (ag.attribute_group_id = agd.attribute_group_id) WHERE pa.product_id = '" . 
			(int)$product_id . "' AND agd.language_id = '" . (int)$this->config->get('config_language_id') . 
			"' GROUP BY ag.attribute_group_id ORDER BY ag.sort_order, agd.name");
		
		foreach ($product_attribute_group_query->rows as $product_attribute_group) {
			$product_attribute_data = array();
			
			$product_attribute_query = $this->db->query("SELECT a.attribute_id, ad.name, pa.text FROM " . 
				DB_PREFIX . "product_attribute pa LEFT JOIN " . 
				DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . 
				DB_PREFIX . "attribute_description ad ON (a.attribute_id = ad.attribute_id) WHERE pa.product_id = '" . 
				(int)$product_id . "' AND a.attribute_group_id = '" . 
				(int)$product_attribute_group['attribute_group_id'] . "' AND ad.language_id = '" . 
				(int)$this->config->get('config_language_id') . "' AND pa.language_id = '" . 
				(int)$this->config->get('config_language_id') . "' ORDER BY a.sort_order, ad.name");
			
			foreach ($product_attribute_query->rows as $product_attribute) {
				$product_attribute_data[] = array(
					'attribute_id' => $product_attribute['attribute_id'],
					'name'         => $product_attribute['name'],
					'text'         => $product_attribute['text']		 	
				);
			}
			
			$product_attribute_group_data[] = array(
				'attribute_group_id' => $product_attribute_group['attribute_group_id'],
				'name'               => $product_attribute_group['name'],
				'attribute'          => $product_attribute_data
			);			
		}
		
		return $product_attribute_group_data;
	}
			
	public function getProductOptions($product_id) {
		$product_option_data = array();

		$product_option_query = $this->db->query("SELECT * FROM " . 
			DB_PREFIX . "product_option po LEFT JOIN `" . 
			DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . 
			DB_PREFIX . "option_description od ON (o.option_id = od.option_id) WHERE po.product_id = '" . 
			(int)$product_id . "' AND od.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY o.sort_order");
		
		foreach ($product_option_query->rows as $product_option) {
			if ($product_option['type'] == 'select' || $product_option['type'] == 'radio' || 
				$product_option['type'] == 'checkbox' || $product_option['type'] == 'image') {
					
				$product_option_value_data = array();
			
				$product_option_value_query = $this->db->query("SELECT * FROM " . 
					DB_PREFIX . "product_option_value pov LEFT JOIN " . 
					DB_PREFIX . "option_value ov ON (pov.option_value_id = ov.option_value_id) LEFT JOIN " . 
					DB_PREFIX . "option_value_description ovd ON (ov.option_value_id = ovd.option_value_id) WHERE pov.product_id = '" . 
					(int)$product_id . "' AND pov.product_option_id = '" . (int)$product_option['product_option_id'] . 
					"' AND ovd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY ov.sort_order");
				
				foreach ($product_option_value_query->rows as $product_option_value) {
					$product_option_value_data[] = array(
						'product_option_value_id' => $product_option_value['product_option_value_id'],
						'option_value_id'         => $product_option_value['option_value_id'],
						'name'                    => $product_option_value['name'],
						'image'                   => $product_option_value['image'],
						'quantity'                => $product_option_value['quantity'],
						'subtract'                => $product_option_value['subtract'],
						'price'                   => $product_option_value['price'],
						'price_prefix'            => $product_option_value['price_prefix'],
						'weight'                  => $product_option_value['weight'],
						'weight_prefix'           => $product_option_value['weight_prefix']
					);
				}
									
				$product_option_data[] = array(
					'product_option_id' => $product_option['product_option_id'],
					'option_id'         => $product_option['option_id'],
					'name'              => $product_option['name'],
					'type'              => $product_option['type'],
					'option_value'      => $product_option_value_data,
					'required'          => $product_option['required']
				);
			} else {
				$product_option_data[] = array(
					'product_option_id' => $product_option['product_option_id'],
					'option_id'         => $product_option['option_id'],
					'name'              => $product_option['name'],
					'type'              => $product_option['type'],
					'option_value'      => $product_option['option_value'],
					'required'          => $product_option['required']
				);				
			}
      	}
		
		return $product_option_data;
	}
	
	public function getProductDiscounts($product_id) {
		$query = $this->db->query("SELECT * FROM " . 
			DB_PREFIX . "product_discount WHERE product_id = '" . 
			(int)$product_id . "' AND quantity > 1 AND date_start < NOW() AND date_end > NOW() ORDER BY quantity ASC, price ASC");

		return $query->rows;		
	}
		
	public function getProductImages($product_id) {
		$query = $this->db->query("SELECT * FROM " . 
			DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "' ORDER BY sort_order ASC");

		return $query->rows;
	}
	
	public function getProductRelated($product_id, $limit = 10) {
		$product_data = array();

		$query = $this->db->query("SELECT DISTINCT pr.related_id FROM " . 
			DB_PREFIX . "product_related pr JOIN " . 
			DB_PREFIX . "product p ON (pr.related_id = p.product_id) WHERE pr.product_id = '" . 
			(int)$product_id . "' AND p.status = '1' AND p.date_available <= NOW()");
		
		foreach ($query->rows as $result) { 
			$product_data[$result['related_id']] = $this->getProduct($result['related_id']);
		}
		
		return $product_data;
	}
	
	public function getProductRelatedAuto($product_id, $limit = 20) {
		$product_data = array();
		$sql = "SELECT pc.category_id FROM " . DB_PREFIX . "product_to_category pc WHERE pc.product_id = " . $product_id;
		$query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p JOIN " . 
			DB_PREFIX . "product_to_category pc ON p.product_id = pc.product_id WHERE pc.category_id IN(" . 
			$sql . ") AND p.status = '1' AND p.date_available <= NOW() ORDER BY p.viewed DESC, p.date_added DESC LIMIT " . $limit);
		
		foreach ($query->rows as $result) { 		
			$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
		}
		return $product_data;
	}
		
	public function getProductTags($product_id) {
		$query = $this->db->query("SELECT * FROM " . 
			DB_PREFIX . "product_tag WHERE product_id = '" . 
			(int)$product_id . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");

		return $query->rows;
	}
		
	public function getProductLayoutId($product_id) {
		$query = $this->db->query("SELECT * FROM " . 
			DB_PREFIX . "product_to_layout WHERE product_id = '" . (int)$product_id . "'");
		
		if ($query->num_rows) {
			return $query->row['layout_id'];
		} else {
			return  $this->config->get('config_layout_product');
		}
	}
	
	public function getCategories($product_id) {
		$query = $this->db->query("SELECT * FROM " . 
			DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");
		
		return $query->rows;
	}	
		
	public function getTotalProducts($data = array()) {
		$cache = md5(http_build_query($data));
		
		$product_count = $this->cache->get('totalProducts.' . (int)$this->config->get('config_language_id') . '.' . $cache);
		
		if (!$product_count) {
			if(isset($data['keyword'])){
				$keyword = $this->db->escape(utf8_strtolower($data['keyword']));
			}else{
				$keyword = false;
			}
			if(isset($data['category_id'])){
				$categoryIds = $data['category_id'];
				if ($categoryIds) {
					$this->load->model('catalog/category');
					$categories = $this->model_catalog_category->getCategoriesByParentId($categoryIds);
					foreach ($categories as $category_id) {
						$categoryIds .= "," . $category_id;
					}
				}
			}else{
				$categoryIds = false;
			}
			$languageId = (int)$this->config->get('config_language_id');
			
			$sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . 
				DB_PREFIX . "product p LEFT JOIN " . 
				DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id AND pd.language_id = '" . $languageId . "') LEFT JOIN " . 
				DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) LEFT JOIN " . 
				DB_PREFIX . "category_description cd ON (p2c.category_id = cd.category_id AND cd.language_id = '" . $languageId . "') LEFT JOIN " . 
				DB_PREFIX . "product_tag pt ON (p.product_id = pt.product_id AND pt.language_id = '" . $languageId . "')" . 
				" WHERE p.status = '1' AND p.date_available <= NOW()";
			
			if ($categoryIds) {
				$sql .= " AND p2c.category_id IN (" . $categoryIds . ")";			
			}
					
			if ($keyword) {
				if(preg_match("/^\d*$/",$keyword)){
					$sql .= " AND p.product_id LIKE '%" . $keyword . "%'";
				} else {
					$sql .= " AND (LCASE(p.model) LIKE '%" . $keyword . 
						"%' OR LCASE(pd.name) LIKE '%" . $keyword . 
						"%' OR LCASE(pd.description) LIKE '%" . $keyword . 
						"%' OR LCASE(cd.name) LIKE '%" . $keyword . 
						"%' OR LCASE(pt.tag) LIKE '%" . $keyword . "')" ;	
				}
			}
			
			$query = $this->db->query($sql);
			$product_count = $query->row['total'];
			$this->cache->set('totalProducts.' . (int)$this->config->get('config_language_id') . '.' . $cache, $product_count);
		}
		return $product_count;
	}
			
	public function getTotalProductSpecials($data = array()) {
		$cache = md5(http_build_query($data));
		
		$product_count = $this->cache->get('totalProductSpecials.' . (int)$this->config->get('config_language_id') . '.' . $cache);
		
		if (!$product_count) {
			$sql = "SELECT COUNT(DISTINCT ps.product_id) AS total FROM " . 
				DB_PREFIX . "product_special ps LEFT JOIN " . 
				DB_PREFIX . "product p ON (ps.product_id = p.product_id) WHERE";
				
			if (isset($data['promotion_id'])) {
				$sql .= " ps.promotion_activity_id = '" . $data['promotion_id'] . "' AND";
			}	
			$sql .= " p.status = '1' AND p.date_available <= NOW()" . 
				" AND ps.date_start < NOW() AND ps.date_end > NOW()";
			
			$query = $this->db->query($sql);	
			if (isset($query->row['total'])) {
				$product_count = $query->row['total'];
			} else {
				$product_count = 0;	
			}
		
			$this->cache->set('totalProductSpecials.' . (int)$this->config->get('config_language_id') . '.' . $cache, $product_count);
		}
		return $product_count;
	}
	
	public function getTotalLatestProducts($data = array()) {
		$cache = md5(http_build_query($data));
		
		$product_count = $this->cache->get('totalLatestProducts.' . (int)$this->config->get('config_language_id') . '.' . $cache);
		
		if (!$product_count) {
			$sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . 
				DB_PREFIX . "product p LEFT JOIN " . 
				DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) WHERE p.status = '1' AND p.date_available <= NOW()";
					
			if(isset($data['category_id'])){
				$categoryIds = $data['category_id'];
				if ($categoryIds) {
					$this->load->model('catalog/category');
					$categories = $this->model_catalog_category->getCategoriesByParentId($categoryIds);
					foreach ($categories as $category_id) {
						$categoryIds .= "," . $category_id;
					}
					$sql .= " AND p2c.category_id IN (" . $categoryIds . ")";
				}
			}			
			
			$query = $this->db->query($sql);	
			if (isset($query->row['total'])) {
				$product_count = $query->row['total'];
			} else {
				$product_count = 0;	
			}
		
			$this->cache->set('totalLatestProducts.' . (int)$this->config->get('config_language_id') . '.' . $cache, $product_count);
		}
		
		return $product_count;
	}
	
	public function getTotalBestSellerProducts($data = array()) {
		$cache = md5(http_build_query($data));
		
		$product_count = $this->cache->get('totalBestSellerProducts.' . (int)$this->config->get('config_language_id') . '.' . $cache);
		
		if (!$product_count) {
			$sql = "SELECT COUNT(DISTINCT op.product_id) AS total FROM " . 
				DB_PREFIX . "order_product op LEFT JOIN `" . 
				DB_PREFIX . "product` p ON (op.product_id = p.product_id) LEFT JOIN " . 
				DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) WHERE p.status = '1' AND p.date_available <= NOW()";
					
			if(isset($data['category_id'])){
				$categoryIds = $data['category_id'];
				if ($categoryIds) {
					$this->load->model('catalog/category');
					$categories = $this->model_catalog_category->getCategoriesByParentId($categoryIds);
					foreach ($categories as $category_id) {
						$categoryIds .= "," . $category_id;
					}
					$sql .= " AND p2c.category_id IN (" . $categoryIds . ")";
				}
			}
			
			$query = $this->db->query($sql);	
			if (isset($query->row['total'])) {
				$product_count = $query->row['total'];
			} else {
				$product_count = 0;	
			}
		
			$this->cache->set('totalBestSellerProducts.' . (int)$this->config->get('config_language_id') . '.' . $cache, $product_count);
		}
		return $product_count;
	}
		
}
?>